Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(1).間違った列の圧縮方式
先日AWS公式のブログで公開された下記のAmazon Redshiftのパフォーマンスチューニングに関するエントリは、非常にタメになるネタが集約されている素晴らしいエントリでした。このまま実用ドキュメントとして展開・活用出来るのではと思う内容ではありますが、内容の1つ1つを見ていくと、ネタ単体でも見るべきポイントがふんだんに盛り込まれているのでは、と思えました。そこで当エントリでは、そのうちの1つを実際に試してみて、どんな結果が得られるのか、何をすれば良いのかについてもう少し踏み込んだ形で読み解いて行きたいと思います。
『課題 #1: 間違った列の圧縮方式』を実践してみる
この課題では、テーブルのカラムに対して『列圧縮タイプ』を指定していない、もしくは適切な指定がなされていない場合の対応について言及されています。エントリに記載されているように、適切な列圧縮タイプの選択をテーブルカラムに行っておく事でパフォーマンスの向上、ストレージ容量の削減を期待出来ます。せっかくこのような便利な・有用な設定があるのにそれを行っていないのは勿体無いですね。
『列圧縮タイプ』未指定のテーブル洗い出し
列圧縮タイプの詳細については以下エントリをご参照ください。
まず最初のSQLでは、『列圧縮タイプ』未指定のテーブルを洗い出しています。このSQL実行では、SVV_TABLE_INFOテーブルを参照しており、その中のカラム『encoded』の値を使って判定を行っています。"いずれかの列で圧縮エンコードが定義されているかどうかを示す値。"という内容の通り、テーブルカラムの中で1つでも定義されていれば『Y』、そうで無ければ『N』が返って来る様です。SQL実行により以下のような形の結果が得られ、今回のケースで対応すべきテーブル名一覧情報が手に入ります。
# SELECT database, schema || '.' || "table" AS "table", encoded, size FROM svv_table_info WHERE encoded='N' ORDER BY 2; database | table | encoded | size -------------+----------------------------------------+---------+------- xxxxxtestdb | public.orders_ext_upd | N | 120 xxxxxtestdb | public.orders_test | N | 969 : xxxxxtestdb | public.sales_ranking | N | 15
対象テーブルのエンコーディング確認
対象となったテーブルに対して以下のSQLを実行する事で、現状のテーブルカラムに対するエンコーディング指定を確認する事が出来ます。現時点ではテーブルカラムに対する指定は無い状態なので、一律noneという指定となっている事が確認出来ます。
# SELECT
trim(n.nspname || '.' || c.relname) AS "table",
trim(a.attname) AS "column",
format_type(a.atttypid, a.atttypmod) AS "type",
format_encoding(a.attencodingtype::integer) AS "encoding",
a.attsortkeyord AS "sortkey"
FROM
pg_namespace n,
pg_class c,
pg_attribute a
WHERE
n.oid = c.relnamespace
AND c.oid = a.attrelid AND a.attnum > 0
AND NOT a.attisdropped and n.nspname NOT IN ('information_schema','pg_catalog','pg_toast')
AND format_encoding(a.attencodingtype::integer) = 'none'
AND c.relkind='r' AND a.attsortkeyord != 1
AND trim(n.nspname || '.' || c.relname) = 'public.orders_test'
ORDER BY
n.nspname, c.relname, a.attnum;
table | column | type | encoding | sortkey
--------------------+----------------------+------------------------+----------+---------
public.orders_test | order_date | date | none | 2
public.orders_test | priority | character varying(12) | none | 0
public.orders_test | quantity | smallint | none | 0
public.orders_test | sales | double precision | none | 0
public.orders_test | discount_rate | double precision | none | 0
public.orders_test | ship_mode | character varying(20) | none | 0
public.orders_test | profit | integer | none | 0
public.orders_test | unit_price | integer | none | 0
public.orders_test | ad_expenses | integer | none | 0
public.orders_test | shipping_cost | integer | none | 0
public.orders_test | customer_name | character varying(50) | none | 0
public.orders_test | prefecture | character varying(12) | none | 0
public.orders_test | city | character varying(20) | none | 0
public.orders_test | area | character varying(12) | none | 0
public.orders_test | shop_name | character varying(20) | none | 0
public.orders_test | customer_segment | character varying(30) | none | 0
public.orders_test | product_category | character varying(30) | none | 0
public.orders_test | product_sub_category | character varying(100) | none | 0
public.orders_test | product_id | character varying(10) | none | 0
public.orders_test | product_name | character varying(100) | none | 0
public.orders_test | product_description | character varying(200) | none | 0
public.orders_test | product_container | character varying(100) | none | 0
public.orders_test | base_margin | double precision | none | 0
public.orders_test | supplier | character varying(30) | none | 0
public.orders_test | deliver_date | date | none | 0
public.orders_test | ship_date | date | none | 0
(26 rows)
また、上記の内容(列圧縮タイプ)はpg_table_defでも確認する事が出来ます。...ってアレ、上記のクエリだとSQL実行結果のソートがされていないっぽいですね。状況確認するのであればpg_table_defを眺めるだけでも問題無いかも知れません。
# SELECT * FROM pg_table_def WHERE schemaname = 'public' AND tablename = 'orders_test'; schemaname | tablename | column | type | encoding | distkey | sortkey | notnull ------------+-------------+----------------------+------------------------+----------+---------+---------+--------- public | orders_test | order_id | integer | none | t | 1 | t public | orders_test | order_date | date | none | f | 2 | t public | orders_test | priority | character varying(12) | none | f | 0 | t public | orders_test | quantity | smallint | none | f | 0 | t public | orders_test | sales | double precision | none | f | 0 | f public | orders_test | discount_rate | double precision | none | f | 0 | f public | orders_test | ship_mode | character varying(20) | none | f | 0 | t public | orders_test | profit | integer | none | f | 0 | t public | orders_test | unit_price | integer | none | f | 0 | t public | orders_test | ad_expenses | integer | none | f | 0 | t public | orders_test | shipping_cost | integer | none | f | 0 | t public | orders_test | customer_name | character varying(50) | none | f | 0 | t public | orders_test | prefecture | character varying(12) | none | f | 0 | t public | orders_test | city | character varying(20) | none | f | 0 | t public | orders_test | area | character varying(12) | none | f | 0 | t public | orders_test | shop_name | character varying(20) | none | f | 0 | t public | orders_test | customer_segment | character varying(30) | none | f | 0 | t public | orders_test | product_category | character varying(30) | none | f | 0 | t public | orders_test | product_sub_category | character varying(100) | none | f | 0 | t public | orders_test | product_id | character varying(10) | none | f | 0 | t public | orders_test | product_name | character varying(100) | none | f | 0 | t public | orders_test | product_description | character varying(200) | none | f | 0 | t public | orders_test | product_container | character varying(100) | none | f | 0 | t public | orders_test | base_margin | double precision | none | f | 0 | f public | orders_test | supplier | character varying(30) | none | f | 0 | t public | orders_test | deliver_date | date | none | f | 0 | t public | orders_test | ship_date | date | none | f | 0 | t (27 rows)
適切な列圧縮エンコーディングの確認・変更
変更すべき列圧縮エンコーディングの内容についてはANALYZE COMPRESSIONコマンドを用いて確認します。今回実行したテーブルは件数が200万件以上あったので特に問題無かったですが、ドキュメントによるとデフォルト指定だと100000となっており、この数値以下だと結果はあまり当てにならないとの事なのでこの辺りは注意が必要です。
# SELECT COUNT(*) FROM public.orders_test;
count
---------
2142464
(1 row)
# ANALYZE COMPRESSION public.orders_test;
Table | Column | Encoding
-------------+----------------------+-----------
orders_test | order_id | lzo
orders_test | order_date | lzo
orders_test | priority | runlength
orders_test | quantity | lzo
orders_test | sales | runlength
orders_test | discount_rate | bytedict
orders_test | ship_mode | lzo
orders_test | profit | lzo
orders_test | unit_price | lzo
orders_test | ad_expenses | lzo
orders_test | shipping_cost | lzo
orders_test | customer_name | runlength
orders_test | prefecture | lzo
orders_test | city | lzo
orders_test | area | lzo
orders_test | shop_name | lzo
orders_test | customer_segment | runlength
orders_test | product_category | lzo
orders_test | product_sub_category | lzo
orders_test | product_id | lzo
orders_test | product_name | lzo
orders_test | product_description | lzo
orders_test | product_container | lzo
orders_test | base_margin | bytedict
orders_test | supplier | lzo
orders_test | deliver_date | lzo
orders_test | ship_date | lzo
(27 rows)
エンコーディングが完了したらディープコピー操作でデータをごそっと入れ替える事で対応が行えます。
# CREATE TABLE public.orders_test_new ( ... ); /** ANALYZE EXPRESSIONコマンドで得られた列圧縮エンコーディングを踏まえたテーブルを作成 */ # INSERT INTO public.orders_test_new SELECT * FROM public.orders_test ...; /** データを全件newに投入 */ # ALTER TABLE public.orders_test RENAME TO public.orders_test_old; /** テーブル名変更(無印->old) */ # ALTER TABLE public.orders_test_new RENAME TO public.orders_test; /** テーブル名変更(new->無印) */
また、本家エントリ内で紹介されている様にAmazon Redshift Column Encoding Utilityを活用するというのもオススメかと思います。こちらは適切なエンコーディングの確認とデータ移行まで行ってくれる便利な代物です。詳細については過去のブログエントリで紹介していますのでご参照ください。
- Amazon Redshift: テーブル列圧縮定義の診断・テーブル間データ移行ツール『Amazon Redshift Column Encoding Utility』 ｜ Developers.IO
まとめ
以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック1つめ、"間違った列の圧縮方式"に関する対処方法のご紹介でした。列圧縮タイプの設定はテーブル作成時、即ち最初期にのみ行われる作業となるため、ついうっかり定義し忘れてしまい以降はずっとそのまま...という事も無くはないと思います。新しいテーブル作成の際には適宜この設定が行われているかを確認し、必要に応じて最適なエンコーディング指定を行っておきたいところですね。
また、2つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
